<?php

// NILAI PERNEGARA
	public function nilai_perhs($d1='',$d2='',$batas='')
	{
		if($this->input->get('count')=='yes')
		{
			$d1 = ID_DateToMySQL($this->input->post('d1'));
			$d2 = ID_DateToMySQL($this->input->post('d2'));
			$batas = $this->input->post('jml');

			$sql = "SELECT SUM(d.nilai) as total
			FROM header h 
			LEFT JOIN detail d ON d.header_id = h.id 
			LEFT JOIN negara n ON h.Kode_Negara_Muat = n.kode 
			WHERE  h.Tanggal_COI >= '$d1' AND h.Tanggal_COI <= '$d2'";

			echo number_format($this->db->query($sql)->row()->total,2);
			die();
		}
		if( $this->input->post('doPrint') == 'yes' || $this->input->get('doExcel') == 'yes')
		{
			if(!$this->input->get('doExcel') == 'yes'){
				$d1 = ID_DateToMySQL($this->input->post('d1'));
				$d2 = ID_DateToMySQL($this->input->post('d2'));
				$batas = $this->input->post('jml');
			}

			/* START */
			$sql = "SELECT SUM(d.nilai) as jml,DATE_FORMAT(h.Tanggal_COI,'%Y-%m') as bln,n.uraian as negara_muat
			FROM header h 
			LEFT JOIN detail d ON d.header_id = h.id  
			LEFT JOIN negara n ON h.Kode_Negara_Muat = n.kode 
			WHERE  h.Tanggal_COI >= '$d1' AND h.Tanggal_COI <= '$d2'
			GROUP BY h.Kode_Negara_Muat, bln ORDER BY jml DESC, bln ASC";

			
			// FORMATTING

			$bln_tahun_list   = array();
			$negara_muat_list = array();

			$table_data = array();

			$rs = $this->db->query($sql)->result();
			
			$jml_semuanya = 0;

			//$jumlah_baris = count($rs);
			$batas = !$batas ? 1000 : $batas;

			foreach ($rs as $r) {
				if( !in_array($r->bln, $bln_tahun_list) )
				{
					$bln_tahun_list[] = $r->bln;
				}
				if( !in_array($r->negara_muat, $negara_muat_list) )
				{
					$negara_muat_list[] = $r->negara_muat;
				}
				///

				$key = $r->negara_muat;
				if( !isset($table_data[$key]) )
				{
					$table_data[$key] = array();
				}

				$bln = $r->bln;
				$jml = $r->jml;
				if(!isset($table_data[$key][$bln]))
				{
					$table_data[$key][$bln] = $jml;	
				}
				$jml_semuanya += $r->jml;
			}
			//echo 'Jumlah Semuanya :' . $jml_semuanya;
			$data = array(
				'bln_tahun'		=> $bln_tahun_list,
				'negara_muat'	=> $negara_muat_list,
				'table_data'	=> $table_data
			);

			// CREATE INITIAL TABLE
			$bln_tahun_column = array();
			foreach ($bln_tahun_list as $bth) {
				$bln_tahun_column[] = date('F Y',strtotime($bth . '-01'));
			}

			$table_column = array_merge(array('Negara Muat'),$bln_tahun_column,array('Jumlah','% Tase'));

			//print_r($table_column);

			$table = '';
			$table_open = '<table align="center" border="2" cellpadding="4" colspacing="4" style="border-collapse:collapse;">';
			$table .= '<tr><th style="background:#cecece" nowrap>'.implode('</th><th style="background:#cecece" nowrap>', $table_column) . '</th></tr>';

			// STORE INITIAL JML PER BLN TAHUN

			$jml_bth_total_sum = array();
			$others_column = array('key'=>'Others');
			
			foreach ($bln_tahun_list as $bth) {
				$jml_bth_total_sum[$bth]=0;
				$others_column[$bth]=0;
			}
			$jml_bth_total_sum['total']= 0;
			$jml_bth_total_sum['percentage'] = 0.0;

			$others_column['total']= 0;
			$others_column['percentage'] = 0.0;

			// PERCENTAGE FOR PIE CHART

			$percentage_for_pie = array();

			// if($batas > 0 ){
			// 	$percentage_for_pie['Others'] = 0;
			// }

			// ITERATION TERHADAP BATAS 
			$iter = 1;
			

			foreach ($data['table_data'] as $key => $val) {
				if( $iter > $batas )
				{
					$jml_bth_total = 0;
					foreach ($bln_tahun_list as $bth) {
						$jml_bth = isset($val[$bth]) ? $val[$bth] : 0;
						//$table .= '<td align="center">'.$jml_bth.'</td>';
						// INITIAL COLUMN FOR OTHERS AT CURRENT BTH
						//$others_column[$bth] = 0;
						
						// STORE SUM PERCOLUMN BLN TAHUN
						$jml_bth_total_sum[$bth] += $jml_bth;

						$others_column[$bth] += $jml_bth;
						
						$jml_bth_total += $jml_bth;
					}
					$jml_bth_total_sum['total'] += $jml_bth_total;
					$jml_bth_total_sum['percentage'] += ($jml_bth_total/$jml_semuanya)*100;

	 				$others_column['total'] += $jml_bth_total;
					$others_column['percentage'] += ($jml_bth_total/$jml_semuanya)*100;

					$percentage = number_format(($jml_bth_total/$jml_semuanya)*100,0);
					
					//$percentage =;
				}
				else
				{
					$table .= '<tr>';
					$table .= '<td nowrap>'.$key.'</td>';
					// ISI TIAP BULAN TAHUN
					$jml_bth_total = 0;
					foreach ($bln_tahun_list as $bth) {
						$jml_bth = isset($val[$bth]) ? $val[$bth] : 0;
						$table .= '<td align="right" nowrap>'.number_format($jml_bth,2).'</td>';
						// STORE SUM PERCOLUMN BLN TAHUN
						$jml_bth_total_sum[$bth] += $jml_bth;
						
						$jml_bth_total += $jml_bth;
					}
					// STORE SUM COLUMN JML TOTAL
					$jml_bth_total_sum['total'] += $jml_bth_total;
					$jml_bth_total_sum['percentage'] += ($jml_bth_total/$jml_semuanya)*100;
					$percentage = number_format(($jml_bth_total/$jml_semuanya)*100,0);

					$table .= '<td align="right" nowrap><b>'.number_format($jml_bth_total,2).'</b></td>';
					$table .= '<td align="right" nowrap>'.$percentage.' %</td>';
					// ISI JUMLAH DAN PROSENTASE
					$table .= '</tr>';		

					$percentage_for_pie[$key] = $percentage . '%';
				}
				// ADD ITERATION
				$iter += 1;	
			}
			if($iter > $batas )
			{
				foreach ($bln_tahun_list as $bth) {
					$others_column[$bth] = number_format($others_column[$bth],2);
				}
				$others_column['total'] = number_format($others_column['total'],2);
				$others_column['percentage'] = number_format($others_column['percentage'],0) . ' %';
				$percentage_for_pie['Others'] = $others_column['percentage'];
				$table .= '<tr><th style="background:#dedede" align="left" nowrap>'.implode('</th><th nowrap style="background:#dedede" align="right">', array_values($others_column)) . '</th></tr>';
			}
			$table .= '<tr>';
			// SUMARY
			foreach ($bln_tahun_list as $bth) {
				$jml_bth_total_sum[$bth] = number_format($jml_bth_total_sum[$bth],2);
			}
			$jml_bth_total_sum['total'] = number_format($jml_bth_total_sum['total'],2);
			$jml_bth_total_sum['percentage'] = number_format($jml_bth_total_sum['percentage'],0) . ' %';
			$total_sum = array_merge(
				array('<b>Jumlah</b>'),
				array_values($jml_bth_total_sum)
			);
			$table .= '<tr><th nowrap style="background:#cecece" align="right">'.implode('</th><th nowrap style="background:#cecece" align="right">', $total_sum) . '</th></tr>';
			$table .= '</tr>';
			$table .= '</table>';

			
			// CREATE PIE CHART
			// Standard inclusions   
			$pchart_path = APPPATH."third_party/pchart";
			include(APPPATH."third_party/pchart/pData.class");
			include(APPPATH."third_party/pchart/pChart.class");

			// Dataset definition 
			$DataSet = new pData;
			

			$DataSet->AddPoint(array_values($percentage_for_pie),"Serie1");
			$DataSet->AddPoint(array_keys($percentage_for_pie),"Serie2");


			$DataSet->AddAllSeries();
			$DataSet->SetAbsciseLabelSerie("Serie2");

			// Initialise the graph
			$Test = new pChart(640,420);
			$Test->loadColorPalette("$pchart_path/Sample/softtones.txt");
			//$Test->drawFilledRoundedRectangle(7,7,293,193,5,240,240,240);
			//$Test->drawRoundedRectangle(5,5,295,195,5,230,230,230);

			// This will draw a shadow under the pie chart
			//$Test->drawFilledCircle(122,102,70,200,200,200);

			// Draw the pie chart
			$Test->setFontProperties("$pchart_path/Fonts/tahoma.ttf",8);
			$Test->drawBasicPieGraph($DataSet->GetData(),$DataSet->GetDataDescription(),190,180,150,PIE_PERCENTAGE);
			//$Test->drawBasicPieGraph($DataSet->GetData(),$DataSet->GetDataDescription(),120,100,70,PIE_PERCENTAGE,255,255,218);
			$Test->drawPieLegend(430,15,$DataSet->GetData(),$DataSet->GetDataDescription(),250,250,250);

			$pie_filename = "pie".microtime().".png";
			$Test->Render("./assets/images/$pie_filename");


			///////////////
			//$filename ="cetak.xls";
			//header('Content-type: application/excel');
				//$filename = 'filename.xls';
			//header('Content-Disposition: attachment; filename='.$filename);
			$table_open .= '<tr><td colspan="'.(count($bln_tahun_column)+3).'" align="center"><h3>Tabel Nilai Per HS Sesuai Permendag</h3>Periode : '.date('d/m/Y',strtotime($d1)).' s.d '. date('d/m/Y',strtotime($d2)).'</td></tr>';
			$table_open .= '<tr><td style="width:640px;height:380px" colspan="'.(count($bln_tahun_column)+3).'"><img src="'.site_url("assets/images/$pie_filename").'" /></td><tr/>';
			//echo($table_open . $table);

			//$doExcel = 'yes';
			if($this->input->get('doExcel') == 'yes')
			{
				header('Content-type: application/excel');
				$filename = 'tabel_nilai_perhs.xls';
				header('Content-Disposition: attachment; filename='.$filename);

				$data = '<html xmlns:x="urn:schemas-microsoft-com:office:excel">
				<head>
				    <!--[if gte mso 9]>
				    <xml>
				        <x:ExcelWorkbook>
				            <x:ExcelWorksheets>
				                <x:ExcelWorksheet>
				                    <x:Name>Sheet 1</x:Name>
				                    <x:WorksheetOptions>
				                        <x:Print>
				                            <x:ValidPrinterInfo/>
				                        </x:Print>
				                    </x:WorksheetOptions>
				                </x:ExcelWorksheet>
				            </x:ExcelWorksheets>
				        </x:ExcelWorkbook>
				    </xml>
				    <![endif]-->
				</head>

				<body>
				   '.$table_open . $table.'
				</body></html>';echo $data;
			}else{
				$data = array(
					'back_url' => site_url('rekap/nilai_perhs'),
					'content' => $table_open . $table,
					'excel_url' => site_url("rekap/nilai_perhs/$d1/$d2/$batas"),
					'title' => 'Tabel Pengapalan Pernegara Periode : '.date('d/m/Y',strtotime($d1)).' s.d '. date('d/m/Y',strtotime($d2))
				);
				$this->load->view('rekap/main',$data) ;
			}
			/* END */ 
		}
		else
		{
			$this->load->view('rekap/_tabel_nilai_perhs');
		}
		
	}